/**
 * 
 */
package com.wzl.qs.db;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.text.format.Time;

import com.wzl.qs.DateTimeUtils;

/**
 * @author wzl
 * 
 */
public class DbAdapter {

	public static final String DATABASE_NAME = "qs";
	public static final String TABLE_NAME = "ENotes";
	public static final String NID_FIELD = "Nid";
	public static final String NAME_FIELD = "Name";
	public static final String PHOTOPATH_FIELD = "PhotoPath";
	public static final String AUDIOPATH_FIELD = "AudioPath";
	public static final String VIDEOPATH_FIELD = "VideoPath";
	public static final String DESCRIPTION_FIELD = "Description";
	public static final String INPUTDATE_FIELD = "InputDate";
	public static final String INPUTTIME_FIELD = "InputTime";
	public static final String POPULARITY_FIELD = "Popularity";
	public static final String THUMBERNAIL_FIELD = "ThunberNail";

	public static final String[] ALL_FIELDS = new String[] { NID_FIELD,
			NAME_FIELD, PHOTOPATH_FIELD, AUDIOPATH_FIELD, VIDEOPATH_FIELD,
			DESCRIPTION_FIELD, INPUTDATE_FIELD, INPUTTIME_FIELD,
			POPULARITY_FIELD, THUMBERNAIL_FIELD };

	public static final int DATABASE_VERSION = 1;

	public static final String CREATE_TABLE = "create table " + TABLE_NAME
			+ " (" + NID_FIELD + " integer primary key autoincrement, "
			+ NAME_FIELD + " text, " + PHOTOPATH_FIELD + " text, "
			+ AUDIOPATH_FIELD + " text, " + VIDEOPATH_FIELD + " text, "
			+ DESCRIPTION_FIELD + " text, " + INPUTDATE_FIELD + " text, "
			+ INPUTTIME_FIELD + " text, " + POPULARITY_FIELD + " integer, "
			+ THUMBERNAIL_FIELD + " text" + ");";

	private final Context context;
	private SQLiteDatabase db;
	private DbHelper dbHelper;

	public DbAdapter(Context ctx) {
		this.context = ctx;
	}

	public DbAdapter open() throws SQLException {
		this.dbHelper = new DbHelper(this.context);
		this.db = this.dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		this.dbHelper.close();
	}

	public SQLiteDatabase getDatabase() {
		return this.db;
	}

	/**
	 * Insert an e-note to the database
	 * 
	 * @param note
	 */
	public void AddENote(ENote note) {
		ContentValues cv = new ContentValues();
		cv.put(DbAdapter.NAME_FIELD, note.getName());
		cv.put(DbAdapter.PHOTOPATH_FIELD, note.getPhotoPath());
		cv.put(DbAdapter.AUDIOPATH_FIELD, note.getAudioPath());
		cv.put(DbAdapter.VIDEOPATH_FIELD, note.getVideoPath());
		cv.put(DbAdapter.DESCRIPTION_FIELD, note.getDescription());
		cv.put(DbAdapter.INPUTDATE_FIELD, note.getInputDate());
		cv.put(DbAdapter.INPUTTIME_FIELD, note.getInputTime());
		cv.put(DbAdapter.POPULARITY_FIELD, note.getPopularity());
		cv.put(DbAdapter.THUMBERNAIL_FIELD, note.getThumberNail());
		this.db.insert(TABLE_NAME, null, cv);
	}

	/**
	 * Delete the e-note with the given note id.
	 * 
	 * @param nid
	 */
	public void DeleteENote(int nid) {
		String where = " where " + NID_FIELD + "=" + nid;
		String sql = "delete from " + TABLE_NAME + where;
		this.db.execSQL(sql);
	}

	public ENote GetENote(int nid) {
		String selection = NID_FIELD + "=" + nid;
		Cursor cursor = this.db.query(TABLE_NAME, ALL_FIELDS, selection, null,
				null, null, null);
		ENote note = null;
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			note = CreateNoteFromCursor(cursor);
		}
		cursor.close();
		return note;
	}

	private ENote CreateNoteFromCursor(Cursor cursor) {
		ENote note = new ENote();
		note.setNid(cursor.getInt(0));
		note.setName(cursor.getString(1));
		note.setPhotoPath(cursor.getString(2));
		note.setAudioPath(cursor.getString(3));
		note.setVideoPath(cursor.getString(4));
		note.setDescription(cursor.getString(5));
		note.setInputDate(cursor.getString(6));
		note.setInputTime(cursor.getString(7));
		note.setPopularity(cursor.getInt(8));
		note.setThumberNail(cursor.getString(9));
		return note;
	}

	/**
	 * Get all the notes from the table.
	 * 
	 * @return
	 */
	public ArrayList<ENote> GetAllENotes() {
		Cursor cursor = this.db.query(TABLE_NAME, ALL_FIELDS, null, null, null,
				null, INPUTTIME_FIELD + " desc");
		ArrayList<ENote> notes = new ArrayList<ENote>();
		int num = cursor.getCount();
		if (num > 0) {
			cursor.moveToFirst();
		}
		for (int count = 0; count < num; count++) {
			ENote note = CreateNoteFromCursor(cursor);
			notes.add(note);
			cursor.moveToNext();
		}
		cursor.close();
		return notes;
	}

	public ArrayList<ENote> GetFavoriteENotes(int numFavorites) {
		Cursor cursor = this.db.query(TABLE_NAME, ALL_FIELDS, null, null, null,
				null, POPULARITY_FIELD + " desc");
		ArrayList<ENote> notes = new ArrayList<ENote>();
		cursor.moveToFirst();
		for (int count = 0; count < numFavorites && count < cursor.getCount(); count++) {
			ENote note = CreateNoteFromCursor(cursor);
			notes.add(note);
			cursor.moveToNext();
		}
		cursor.close();
		return notes;
	}

	public void InsertATestNote() {
		ENote note = new ENote();
		note.setName("Wu Wei");
		note.setPhotoPath("/sdcard/test.jpg");
		note.setAudioPath("/sdcard/test.3gpp");
		note.setDescription("Smoking is bad for your health");
		Time tt = new Time();
		tt.setToNow();
		String date = DateTimeUtils.getDate(tt);
		String time = DateTimeUtils.getTime(tt);
		note.setInputDate(date);
		note.setInputTime(time);
		note.setPopularity(0);
		note.setThumberNail("/sdcard/test_small.jpg");
		this.AddENote(note);
	}

	public void increasePopularity(int nid, int num) {
		ENote note = GetENote(nid);
		int popularity = note.getPopularity();
		popularity = popularity + num;
		String updatesql = "update " + TABLE_NAME + " set " + POPULARITY_FIELD
				+ "=" + popularity + " where nid=" + nid + "";
		this.db.execSQL(updatesql);
	}
}
